


clear all
set more off
set memo 4g
set matsize 800
capture log close


cd ~/Crime


log using Sexratio_crime.log, text replace


********************************************************************************************

* Census 1990: residential sex ratios

********************************************************************************************

	use census90.dta, clear

	replace birthyr=1000+birthyr

	tab birthyr, m

	g xx=region1/10000     
        g id=int(x)
	drop xx region1

	g gender=(sex==1)
	drop sex
	
	
	sort id birthyr

	ge province=id
	collapse (count) id (mean) gender, by(province birthyr)


	rename id c
	rename province id
	rename gender sex
	
	
	save cohort_1990.dta, replace
	clear

	use cohort_1990.dta, clear

	tab id

	tsset id birthyr
	sort id birthyr

	bys id: g year=birthyr+16

	tsset id year
	sort id year

	bys id: g c_16_25_90=c+L.c+L2.c+L3.c+L4.c+L5.c+L6.c+L7.c+L8.c+L9.c
	label var c_16_25_90 "cohort size 16-25 in 90 census"

	bys id: g c_16_25_90_m=sex*c+L.sex*L.c+L2.sex*L2.c+L3.sex*L3.c+L4.sex*L4.c+L5.sex*L5.c+L6.sex*L6.c+L7.sex*L7.c+L8.sex*L8.c+L9.sex*L9.c
	label var c_16_25_90_m "male cohort size 16-25 in 90 census"
	
	g c_16_25_90_f=c_16_25_90-c_16_25_90_m
	label var c_16_25_90_f "female cohort size 16-25 in 90 census"
	
	g ratio_16_25_90=c_16_25_90_m/c_16_25_90_f
	label var ratio_16_25_90 "sex ratio for cohort 16-25 in 90 census"
	
	g lc_16_25_90=ln(c_16_25_90)
	g lc_16_25_90_m=ln(c_16_25_90_m)
	g lc_16_25_90_f=ln(c_16_25_90_f)
	g lratio_16_25_90=ln(ratio_16_25_90)
       
    bys id: g c_26_45_90=L10.c+L11.c+L12.c+L13.c+L14.c+L15.c+L16.c+L17.c+L18.c+L19.c+L20.c+L21.c+L22.c+L23.c+L24.c+L25.c+L26.c+L27.c+L28.c+L29.c
	label var c_26_45_90 "cohort size 26-45 in 82 census"

	bys id: g c_26_45_90_m=L10.sex*L10.c+L11.sex*L11.c+L12.sex*L12.c+L13.sex*L13.c+L14.sex*L14.c+L15.sex*L15.c+L16.sex*L16.c+L17.sex*L17.c+L18.sex*L18.c+L19.sex*L19.c+L20.sex*L20.c+L21.sex*L21.c+L22.sex*L22.c+L23.sex*L23.c+L24.sex*L24.c+L25.sex*L25.c+L26.sex*L26.c+L27.sex*L27.c+L28.sex*L28.c+L29.sex*L29.c
	label var c_26_45_90_m "male cohort size 26-45 in 82 census"
	
	g c_26_45_90_f=c_26_45_90-c_26_45_90_m
	label var c_26_45_90_f "female cohort size 26-45 in 90 census"
	
	g ratio_26_45_90=c_26_45_90_m/c_26_45_90_f
	label var ratio_26_45_90 "sex ratio for cohort 25-45 in 90 census"
	
	g lc_26_45_90=ln(c_26_45_90)
	g lc_26_45_90_m=ln(c_26_45_90_m)
	g lc_26_45_90_f=ln(c_26_45_90_f)
	g lratio_26_45_90=ln(ratio_26_45_90)
	
	
	bys id: g c_46_65_90=L30.c+L31.c+L32.c+L33.c+L34.c+L35.c+L36.c+L37.c+L38.c+L39.c+L40.c+L41.c+L42.c+L43.c+L44.c+L45.c+L46.c+L47.c+L48.c+L49.c
	label var c_46_65_90 "cohort size 46-65 in 82 census"

	bys id: g c_46_65_90_m=L30.sex*L30.c+L31.sex*L31.c+L32.sex*L32.c+L33.sex*L33.c+L34.sex*L34.c+L35.sex*L35.c+L36.sex*L36.c+L37.sex*L37.c+L38.sex*L38.c+L39.sex*L39.c+L40.sex*L40.c+L41.sex*L41.c+L42.sex*L42.c+L43.sex*L43.c+L44.sex*L44.c+L45.sex*L45.c+L46.sex*L46.c+L47.sex*L47.c+L48.sex*L48.c+L49.sex*L49.c
	label var c_46_65_90_m "male cohort size 46-65 in 82 census"	     		   		
     		   		   		
    g c_46_65_90_f=c_46_65_90-c_46_65_90_m
	label var c_46_65_90_f "female cohort size 46-65 in 90 census"
	
	g ratio_46_65_90=c_46_65_90_m/c_46_65_90_f
	label var ratio_46_65_90 "sex ratio for cohort 46-65 in 90 census"
	
	g lc_46_65_90=ln(c_46_65_90)
	g lc_46_65_90_m=ln(c_46_65_90_m)
	g lc_46_65_90_f=ln(c_46_65_90_f)
	g lratio_46_65_90=ln(ratio_46_65_90)   		
     	

	drop birthyr c sex 
	
    save restat_ratio_90_16_25.dta, replace
      
    clear


	use cohort_1990.dta, clear

	tab id


	bys id: g year=birthyr+10

	tsset id year
	sort id year


	bys id: g c_10_15_90=c+L.c+L2.c+L3.c+L4.c+L5.c
	label var c_10_15_90 "cohort size 10-15 in 90 census"

	bys id: g c_10_15_90_m=sex*c+L.sex*L.c+L2.sex*L2.c+L3.sex*L3.c+L4.sex*L4.c+L5.sex*L5.c
	label var c_10_15_90_m "male cohort size 10-15 in 90 census"
	
	g c_10_15_90_f=c_10_15_90-c_10_15_90_m
	label var c_10_15_90_f "female cohort size 10-15 in 90 census"
	
	g ratio_10_15_90=c_10_15_90_m/c_10_15_90_f
	label var ratio_10_15_90 "sex ratio for cohort 10-15 in 90 census"
	
	g lc_10_15_90=ln(c_10_15_90)
	g lc_10_15_90_m=ln(c_10_15_90_m)
	g lc_10_15_90_f=ln(c_10_15_90_f)
	g lratio_10_15_90=ln(ratio_10_15_90)
	

	drop birthyr c sex 
	
    save restat_ratio_90_10_15.dta, replace
      
    clear
     

	use restat_ratio_90_10_15.dta, clear
	merge id year using restat_ratio_90_16_25.dta, unique sort

	drop _merge
	sort id year
	tsset id year

	keep if year>=1988 & year<=2004
	
	keep id year ratio*
	
	rename ratio_16_25_90  mf90x1625
	rename ratio_10_15_90  mf90x1015
	rename ratio_26_45_90  mf90x2645
	rename ratio_46_65_90  mf90x4665

    save sexratio_1990.dta, replace
	
	clear
	
	
********************************************************************************************

* Census 2000: Birth sex ratio, residential sex ratio, and weighted sex ratio

********************************************************************************************
	


use Census00.dta, clear
keep id r03 r041 r08*


rename r041 byear
drop if id==.

ge male=r03==1

drop r03 

ge prov=int(id/(10^16))      


ge prov5051=(prov==50|prov==51)
replace prov=51 if prov5051==1 /* Folds Chongqing into Sichuan */

                                                                                                                                                                                       
* generate birth province

ge bprov=prov
replace bprov=r082 if r081==3 

ge bprov5051=(bprov==50|bprov==51)
replace bprov=51 if bprov5051==1 /* Folds Chongqing into Sichuan */
drop bprov5051 prov5051

sort bprov 
drop id r* 
keep if prov<=65
keep if bprov<=65
keep if byear>1920 & byear<1995
save temp, replace



forvalues y=1988(1)2004 {
ge z1015`y'=0
replace z1015`y'=1 if byear>=`y'-15 & byear<=`y'-10
ge z1625`y'=0
replace z1625`y'=1 if byear>=`y'-25 & byear<=`y'-16
ge z2645`y'=0
replace z2645`y'=1 if byear>=`y'-45 & byear<=`y'-26
ge z4665`y'=0
replace z4665`y'=1 if byear>=`y'-65 & byear<=`y'-46
}

generate female=1-male 


cd ~/hpc_tmp/Crime
save temp, replace


*************Creating smaller files***************

use temp, clear
keep if byear>=1973 & byear <= 1994
keep male female prov bprov byear z1015*
save z1015, replace 

use temp, clear 
keep if byear>=1963 & byear <= 1988
keep male female prov bprov byear z1625*
save z1625, replace 

use temp, clear 
keep if byear>=1943 & byear <= 1978
keep male female prov bprov byear z2645*
save z2645, replace 

use temp, clear 
keep if byear>=1923 & byear <= 1958
keep male female prov bprov byear z4665*
save z4665, replace 


cd ~/hpc_tmp/Crime
global ages "1015 1625 2645 4665"
global ages1 "1625 2645 4665"

global birth    "11 12 13 14 15 21 22 23 31 32 33 34 35 36 37 41 42 43 44 45 46 51 52 53 54 61 62 63 64 65 "
global birth1    " 12 13 14 15 21 22 23 31 32 33 34 35 36 37 41 42 43 44 45 46 51 52 53 54 61 62 63 64 65 "

global resid    "11 12 13 14 15 21 22 23 31 32 33 34 35 36 37 41 42 43 44 45 46 51 52 53 54 61 62 63 64 65 "
global resid1    " 12 13 14 15 21 22 23 31 32 33 34 35 36 37 41 42 43 44 45 46 51 52 53 54 61 62 63 64 65 "


********Create sex ratios by birth province************

foreach a in $ages{
    forvalues y=1988(1)2004 {
    use z`a', clear
    keep if z`a'`y'==1
    sort bprov 
    collapse (sum) menb`a'=male womb`a'=female, by(bprov)
    ge year=`y'
    ge mfb`a'=menb`a'/womb`a' 
    save mfb`a'y`y', replace 
    }
       
    use mfb`a'y1988, clear
    forvalues y=1989(1)2004 {
    append using mfb`a'y`y'
    }
    sort bprov year 
    
    save mfb`a', replace 
}


use mfb1015, clear

foreach a in $ages1{
merge bprov year using mfb`a'
drop _merge 
sort bprov year 
}

sort bprov year 
saveold mfb, replace * Data set with sex ratios by province of birth 

********Create sex ratios by province of residence************

foreach a in $ages{
    forvalues y=1988(1)2004 {
    use z`a', clear
    keep if z`a'`y'==1
    sort prov 
    collapse (sum) men`a'=male wom`a'=female, by(prov)
    ge year=`y'
    ge mf`a'=men`a'/wom`a' 
    save mf`a'y`y', replace 
    }
       
    use mf`a'y1988, clear
    forvalues y=1989(1)2004 {
    append using mf`a'y`y'
    }
    sort prov year 
    
    save mf`a', replace 
}


use mf1015, clear
sort prov year 

foreach a in $ages1{
merge prov year using mf`a'
drop _merge 
sort prov year 
}

sort prov year 
saveold mfr, replace * Data set with sex ratios by province of residence 


******** Create weigthed sex ratios ************

foreach a in $ages {
    forvalues y=1988(1)2004 {
    foreach b in $birth {
    
    use if bprov==`b' & z`a'`y'==1 using z`a', clear
    
    ge m`a'b`b'=male 
    collapse (sum) m`a'b`b', by(prov) 
    ge year=`y'
    sort prov year 
    save m`a'b`b'y`y', replace 
    }
    }

        
    foreach b in $birth {
    use m`a'b`b'y1988, clear 
    forvalues y=1989(1)2004 {
    append using m`a'b`b'y`y'
    }
    sort prov year 
    save m`a'b`b', replace 
    }
    
}

foreach a in $ages {
 
    use m`a'b11, clear 
    foreach b in $birth1 {
    merge prov year using m`a'b`b'
    drop _merge 
    sort prov year 
    }
    
    foreach b in $birth {
    replace m`a'b`b'=0 if m`a'b`b'==. 
    }
    
    save m`a'transition, replace 
} 

foreach a in $ages {

    use mfb`a', clear 
        foreach b in $birth{
        ge sr`a'b`b'=mfb`a' if bprov==`b'
        egen srm`a'b`b'=mean(sr`a'b`b'), by(year)
        drop sr`a'b`b'
        rename srm`a'b`b' sr`a'b`b'
        }
    keep bprov year sr* 
    rename bprov id 
    sort id year 
    save sr4weighted`a', replace 
    
    use m`a'transition, clear 
    rename prov id 
    sort id year 
    merge id year using sr4weighted`a' 
    tab _merge 
    drop _merge 
    
    save w`a', replace 
    
    
    generate w`a'=0
    foreach b in $birth{
    replace w`a'=w`a' + m`a'b`b'*sr`a'b`b'
    }
    
    generate M`a'=0 
    foreach b in $birth{
    replace M`a'=M`a' + m`a'b`b'
    }
    
    generate mfw`a'=w`a'/M`a' * The weighted sex ratio for ages `a'
    save mfw`a', replace 

}


foreach a in $ages {
use mfw`a', clear 
keep id year mfw`a' 
sort id year 
save w`a', replace 
}

use w1015, clear 


foreach a in $ages1 {
merge id year using w`a' 
drop _merge
sort id year 
}

sort id year 
saveold mfw, replace /*creates data set with id year weighted sex ratios, 510 obs */


